import sqlite3
import os

DB_FILE = "player_history.db" # 历史记录和播放列表共用一个数据库文件

class HistoryDatabase:
    """封装所有与播放历史和播放列表数据库的交互"""
    def __init__(self):
        self._conn = sqlite3.connect(DB_FILE)
        self._create_tables()

    def _create_tables(self):
        """如果表不存在，则创建它们"""
        cursor = self._conn.cursor()
        # 历史记录表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                filepath TEXT NOT NULL UNIQUE,
                last_played_time DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        # 播放列表表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS playlist (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                filepath TEXT NOT NULL UNIQUE,
                added_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                position INTEGER NOT NULL
            )
        """)
        self._conn.commit()

    # --- 历史记录相关方法 ---

    def add_or_update_history(self, filepath):
        """添加一条记录到历史，如果已存在则更新时间"""
        cursor = self._conn.cursor()
        cursor.execute("""
            REPLACE INTO history (filepath, last_played_time)
            VALUES (?, CURRENT_TIMESTAMP)
        """, (filepath,))
        self._conn.commit()

    def get_all_history(self, limit=20):
        """从数据库加载历史记录并返回"""
        cursor = self._conn.cursor()
        cursor.execute("SELECT filepath FROM history ORDER BY last_played_time DESC LIMIT ?", (limit,))
        return [row[0] for row in cursor.fetchall()]

    def delete_history(self, filepath):
        """根据文件路径删除历史记录"""
        cursor = self._conn.cursor()
        cursor.execute("DELETE FROM history WHERE filepath = ?", (filepath,))
        self._conn.commit()

    # --- 播放列表相关方法 ---

    def add_to_playlist(self, filepath):
        """将文件添加到播放列表，如果已存在则不添加"""
        cursor = self._conn.cursor()
        # 获取当前播放列表的最大position，以便新添加的项排在最后
        cursor.execute("SELECT MAX(position) FROM playlist")
        max_position = cursor.fetchone()[0]
        new_position = (max_position if max_position is not None else -1) + 1 # 从0开始或继续

        try:
            cursor.execute("""
                INSERT INTO playlist (filepath, position)
                VALUES (?, ?)
            """, (filepath, new_position))
            self._conn.commit()
            return True
        except sqlite3.IntegrityError:
            # 如果文件已存在于播放列表中 (UNIQUE 约束)
            return False

    def get_playlist(self):
        """获取播放列表中的所有文件，按position排序"""
        cursor = self._conn.cursor()
        cursor.execute("SELECT filepath FROM playlist ORDER BY position ASC")
        return [row[0] for row in cursor.fetchall()]

    def remove_from_playlist(self, filepath):
        """从播放列表中删除指定文件，并更新后续文件的position"""
        cursor = self._conn.cursor()
        # 首先获取要删除项的position
        cursor.execute("SELECT position FROM playlist WHERE filepath = ?", (filepath,))
        result = cursor.fetchone()
        if result:
            deleted_position = result[0]
            # 删除项
            cursor.execute("DELETE FROM playlist WHERE filepath = ?", (filepath,))
            # 更新后续项的position
            cursor.execute("UPDATE playlist SET position = position - 1 WHERE position > ?", (deleted_position,))
            self._conn.commit()
            return True
        return False

    def clear_playlist(self):
        """清空播放列表"""
        cursor = self._conn.cursor()
        cursor.execute("DELETE FROM playlist")
        self._conn.commit()

    def close(self):
        """关闭数据库连接"""
        if self._conn:
            self._conn.close()